package com.bookStore.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.sql.Statement;

import javax.jws.soap.SOAPBinding.Use;

import com.bookStore.domain.User;

public class UserDao {
	
	@SuppressWarnings("finally")
	public User checkLogin(String email, String password){
		User user = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet  rs = null;
		try {
			conn=DBConn.getConnection();
			String sql = "select * from user where email = ? and password = MD5(?) limit 1";
			ps = conn.prepareStatement(sql);
			ps.setString(1, email);
			ps.setString(2, password);
			rs = ps.executeQuery();
			if(rs.next()){
				user = new User(rs.getInt("id"), rs.getString("email"), rs.getString("password"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs != null){
					rs.close();
				}
				if(ps != null){
					ps.close();
				}
				if(conn != null){
					conn.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			return user;
		}
	}
	
	@SuppressWarnings("finally")
	public Boolean isExistUser(String email){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet  rs = null;
		Boolean isExist = false;
		try {
			conn=DBConn.getConnection();
			String sql = "select count(*) from user where email = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, email);
			rs.first();
			rs = ps.executeQuery();
			if(rs.getInt(1) == 1){
				isExist = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs != null){
					rs.close();
				}
				if(ps != null){
					ps.close();
				}
				if(conn != null){
					conn.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			return isExist;
		}
	}
	
	@SuppressWarnings("finally")
	public Boolean registerUser(String email, String password){
		Boolean isSuccess = false;
		Connection conn = null;
		PreparedStatement ps = null;
		if(this.isExistUser(email) == false){
			try {
				conn=DBConn.getConnection();
				String sql = "insert into user values(?, MD5(?))";
				ps = conn.prepareStatement(sql);
				ps.setString(1, email);
				ps.setString(2, password);
				ps.executeUpdate();
				isSuccess = true;
			} catch (Exception e) {
				e.printStackTrace();
			}finally{
				try {
					if(ps != null){
						ps.close();
					}
					if(conn != null){
						conn.close();
					}
				} catch (Exception e2) {
					e2.printStackTrace();
				}
				return isSuccess;
			}
		}else{
			return isSuccess;
		}
	}
	
	
	// 得到用户表总记录数
    public int getRowCount() throws Exception {
        int rowCount = 0;
        try {
        	Connection conn = DBConn.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select count(*) from user");
            rs.first();
            rowCount = rs.getInt(1);
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        return rowCount;
    }
 
    // 获取分页用户信息
    public List<User> getPagingUser(int startRow, int pageRows)
            throws Exception {
        List<User> users = new ArrayList<User>();
        try {
        	Connection conn = DBConn.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt
                    .executeQuery("select * from user order by id desc limit "
                            + startRow + "," + pageRows);
            while (rs.next()) {
                User user = new User(rs.getInt("id"), rs.getString("email"), rs.getString("password"));
                users.add(user);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        return users;
    }
 
    // 删除用户
    public void deleteUser(int id) throws Exception {
    	Connection conn = DBConn.getConnection();
        Statement stmt = conn.createStatement();
        try {
            stmt.execute("delete from user where id=" + id);
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
    }
    
 // 修改密码
    public void update(int id, String password) throws Exception {
    	Connection conn = DBConn.getConnection();
        Statement stmt = conn.createStatement();
        try {
            stmt.execute("update  user set password='" + password+"' where id='" +id+"'");
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
    }
	
}
